package com.cn.Dao;

import com.cn.base.BaseDao;
import com.cn.entity.UserInfo;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


public class UserDao {
    public UserInfo findByName(UserInfo userInfo) throws SQLException {
        Connection connection = BaseDao.getConn();
        try {
            Statement state=connection.createStatement();
            String sql="select * from user where USER_USERNAME = '" +
                    userInfo.getUsername() + "'";
            ResultSet rs = state.executeQuery(sql);
            userInfo = getUser(rs);
            connection.close();
            return userInfo;
        }catch (Exception e){
            throw e;
        }
    }
    public UserInfo findByName(String username) throws SQLException {
        Connection connection = BaseDao.getConn();
        try {
            Statement state=connection.createStatement();
            String sql="select * from user where USER_USERNAME = '" +
                    username + "'";
            ResultSet rs = state.executeQuery(sql);
            UserInfo userInfo = getUser(rs);
            connection.close();
            return userInfo;
        }catch (Exception e){
            throw e;
        }
    }
    public List<UserInfo> findAll() throws SQLException {
        Connection connection = BaseDao.getConn();
        try {
            Statement state=connection.createStatement();
            String sql="select * from user";
            ResultSet rs = state.executeQuery(sql);

            List<UserInfo> userInfos = getList(rs);
            connection.close();
            return userInfos;
        }catch (Exception e){
            throw e;
        }
    }
    private UserInfo getUser(ResultSet resultSet){
        try {
            UserInfo userInfo = new UserInfo();
            while (resultSet.next()){
                userInfo.setId(resultSet.getInt(1));
                userInfo.setUsername(resultSet.getString(2));
                userInfo.setPassword(resultSet.getString(3));
                userInfo.setName(resultSet.getString(4));
                userInfo.setPhone(resultSet.getString(5));
                userInfo.setDepartmentid(resultSet.getInt(6));
            }
            return userInfo;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
    private List<UserInfo> getList(ResultSet resultSet){
        try {
            List<UserInfo> userInfos=new ArrayList<>();
            while (resultSet.next()){
                UserInfo userInfo = new UserInfo();
                userInfo.setId(resultSet.getInt(1));
                userInfo.setUsername(resultSet.getString(2));
                userInfo.setPassword(resultSet.getString(3));
                userInfo.setName(resultSet.getString(4));
                userInfo.setPhone(resultSet.getString(5));
                userInfo.setDepartmentid(resultSet.getInt(6));
                userInfos.add(userInfo);
            }
            return userInfos;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public void Insert(UserInfo userInfo) throws SQLException {
        Connection connection = BaseDao.getConn();
        try{
            Statement state = connection.createStatement();//容器
            String sql = "insert into user (USER_USERNAME, USER_PASSWORD, USER_NAME, USER_PHONE, DEP_ID) value ('" +
                    userInfo.getUsername() + "','"+
                    userInfo.getPassword() + "','" +
                    userInfo.getName() + "','" +
                    userInfo.getPhone() +"','" +
                    userInfo.getDepartmentid()+
                    "')";
            System.out.println("Insert user sql\n"+sql);
            state.executeUpdate(sql);
        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }finally {
            connection.close();
        }
    }
     public void Update(UserInfo userInfo)throws SQLException{
        Connection connection=BaseDao.getConn();
        try {
            Statement statement=connection.createStatement();
            String sql="update user set USER_PASSWORD='"+userInfo.getPassword()+"'" +
                    ",USER_NAME='"+userInfo.getName()+"',USER_PHONE='"+userInfo.getPhone()+"',DEP_ID='"+userInfo.getDepartmentid()+"'" +
                    "where USER_ID='"+userInfo.getId()+"'";
            statement.executeUpdate(sql);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            connection.close();
        }
     }
    public UserInfo findById(Integer id) throws SQLException {
        Connection connection = BaseDao.getConn();
        try {
            Statement state=connection.createStatement();
            String sql="select * from user where USER_ID = '" +
                    id + "'";
            ResultSet rs = state.executeQuery(sql);
            UserInfo userInfo = getUser(rs);
            connection.close();
            return userInfo;
        }catch (Exception e){
            throw e;
        }
    }
}